MCS 023 Introduction to Database Management Systems
(3 Credits)
Objectives
Database systems are pervasive. They are pesent in every segment of commercial, academic and virtual world. They are required as the backbone of any information system, enterprise resource planning, research activities and other activity that require permanence of data storage. This course provides the basic introduction to database system technologies; and concurrency, security and recovery issues of database management systems.
This course also provides the basic conceptual background necessary to design and develop simple database systems. The major focus in this course is the Relational database model; however, it also discusses about the ER model and distributed databases. This course enables you to write good queries using a standard query language called SQL.
Syllabus
BLOCK 1:
The Database Management System Concepts
Unit 1: The Basic
Concepts
·
Need for a Database Management System
o
The file based
system
o
Limitations
of file based system
o
The Database
Approach
·
The Logical DBMS Architecture
o
Three level architecture of DBMS or logical DBMS architecture
o
Mappings between levels and data independence
o
The need for three level architecture
·
Physical DBMS Architecture
o
DML Precompiler
o
DDL Compiler
o
File Manager
o
Database Manager
o
Query Processor
o
Database Administrator
o
Data files indices and Data Dictionary
·
Commercial
Database Architecture
·
Data
Models
Unit 2:
Relational And ER Models
·
The Relational
Model
o
Domains, Attributes, Tuple and Relation
o
Super keys Candidate keys and Primary keys
for the Relations
·
Relational Constraints
o
Domain Constraint
o
Key Constraint
o
Integrity Constraint
o
Update Operations and Dealing with Constraint Violations
·
Relational Algebra
o
Basic Set Operation
o
Cartesian Product
o
Relational Operations
·
Entity Relationship (ER) Model
o
Entities
o
Attributes
o
Relationships
o
More about Entities and Relationships
o
Defining Relationship for College Database
·
E-R Diagram
·
Conversion of E-R Diagram to Relational
Database
Unit 3: Database
Integrity and Normalisation
·
Relational Database Integrity
o
The Keys
o
Referential Integrity
o
Entity Integrity
·
Redundancy and Associated Problems
·
Single-Valued Dependencies
·
Single-Valued Normalisation
o
The First
Normal
Form
o
The Second
Normal
Form
o
The Third
Normal
Form
o
Boyce Codd
Normal
Form
·
Desirable Properties of Decomposition
o
Attribute Preservation
o
Lossless-join Decomposition
o
Dependency Preservation
o
Lack of redundancy
·
Rules of Data Normalisation
o
Eliminate Repeating Groups
o
Eliminate Redundant Data
o
Eliminate Columns Not Dependent on Key
Unit 4: File Organisation
in DBMS
·
Physical Database Design Issues
·
Storage of Database on Hard Disks
·
File Organisation and Its Types
o
Heap files (Unordered files)
o
Sequential File Organisation
o
Indexed (Indexed Sequential) File Organisation
o
Hashed File Organisation
·
Types of Indexes
·
Index
and Tree Structure
·
Multi-key File Organisation99
o
Need for Multiple Access Paths
o
Multi-list File Organisation
o
Inverted File Organisation
·
Importance of File Organisation in
Databases
BLOCK 2:
Structured Query Language and Transaction Management
Unit 1:
The Structures Query Language
·
What is SQL?
·
Data Definition Language
·
Data Manipulation Language
·
Data Control
·
Database Objects: Views, Sequences,
Indexes and Synonyms
o
Views
o
Sequences
o
Indexes and Synonyms
·
Table Handling
·
Nested Queries
Unit 2: Transactions
and Concurrency Management
·
The Transactions
·
The Concurrent Transactions
·
The Locking Protocol
o
Serialisable Schedules
o
Locks
o
Two Phase Locking
(2PL)
·
Deadlock and its Prevention
·
Optimistic Concurrency Control
Unit 3: Database
Recovery and Security
·
What is Recovery?
o
Kinds of failures
·
Recovery Techniques
·
Security & Integrity
o
Relationship between Security and Integrity
o
Difference between Operating System and Database Security
·
Authorization
Unit 4: Distributed
and Client Server Databases
·
Need for Distributed
Database Systems
·
Structure of Distributed
Database
·
Advantages and Disadvantages
of DDBMS
o
Advantages of Data Distribution
o
Disadvantages of Data Distribution
·
Design of Distributed
Databases
o
Data Replication
o
Data Fragmentation
·
Client Server Databases
o
Emergence of Client Server Architecture
o
Need for Client Server Computing
o
Structure of Client Server Systems
o
Advantages of Client Server Systems
BLOCK 3: Application
Development: Development of a Hospital Management System
·
Need to Develop the Hospital Management System (An HMS)
·
Creating a Database for HMS
·
Developing Front End Forms
·
Reports
·
Using Queries and Record set
BLOCK 4:
Study Centre Management System: A Case Study
·
Software Development Process: Analysis
·
System Designing
·
Issues relating to Software Development, Testing and Maintenance